package com.zhongyouyun.utils;

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.zhongyouyun.other.exception.BizException;
import com.zhongyouyun.other.rescoure.ExcelRescoure;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;

import static javax.xml.bind.JAXBIntrospector.getValue;

public class ExcelUtils {
    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";
    //日志输出
    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);


    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream 读取文件的输入流
     * @param fileType    文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     */
    private static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        //用自带的方法新建工作薄
        //Workbook workbook = WorkbookFactory.create(inputStream);
        //后缀判断有版本转换问题
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    public static <T> void exportALL(LinkedHashMap<String, String> headMap, String title, List<T> dataList, HttpServletRequest request, HttpServletResponse response) {
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        headMap.forEach((k, v) -> {
            writer.addHeaderAlias(k, v);
        });
        // 合并单元格后的标题行，使用默认标题样式
        writer.reset();
        writer.merge(headMap.size() - 1, title);
        writer.setOnlyAlias(true);
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(dataList, true);
        autoSizeColumns(writer.getSheet(), headMap.size());
        //out为OutputStream，需要写出到的目标流
        //response为HttpServletResponse对象
        ServletOutputStream out = null;
        try {
            String fileName = URLEncoder.encode(title, "UTF-8").replaceAll("\\+", "%20");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (Exception e) {
            throw new BizException(1, "导出错误", false);
        } finally {
            // 关闭writer，释放内存
            writer.close();
        }
        //此处记得关闭输出Servlet流
        IoUtil.close(out);

    }

    /**
     * 自动调整列宽
     *
     * @param sheet
     * @param columnNumber
     */
    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
            int maxWith = 256 * 255;
            //限制下最大宽度
            if (newWidth > maxWith) {
                sheet.setColumnWidth(i, maxWith);
            } else if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    public static List<List<String>> readExcelByInputStream(String filename, InputStream inputStream, int start,int sheetat) throws IOException {
        List<List<String>> result = new ArrayList<>();
        // 获得文件名
        Workbook workbook = null;
        if (filename.endsWith(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (filename.endsWith(XLSX)) {

            workbook = new XSSFWorkbook(inputStream);

        } else {
            return result;
        }
        // 获取第一个表单Sheet
        Sheet sheetAt = workbook.getSheetAt(sheetat);
        // 循环获取每一行数据
        for (int i = start; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            List<String> itemString = new ArrayList<>();
            Row row = sheetAt.getRow(i);
            // 读取每一格内容
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {

                Cell cell = row.getCell(index);
               // cell.setCellType(CellType.STRING);
                itemString.add(getStringVal(cell));
            }
            result.add(itemString);
        }
        return result;
    }

    /**
     * 方法描述: 获取单元格的数值
     * 根据单元格的类型确定处理返回的结果
     *
     * @param cell 单元格
     * @date 2022-06-07 09:15
     **/
    private static String getStringVal(Cell cell) {

        if (ObjectUtil.isNotEmpty(cell)) {
            // 判断文件类型
            switch (cell.getCellType()) {
                case BOOLEAN:
                    return cell.getBooleanCellValue() ? "true" : "false";
                case FORMULA:
                    String value;
                    try {
                        value = String.valueOf(cell.getNumericCellValue());
                    } catch (IllegalStateException e) {
                        cell.setCellType(CellType.STRING);
                        value = String.valueOf(cell.getRichStringCellValue());
                    }
                    return value;
                case NUMERIC:
                    cell.setCellType(CellType.STRING);
                    return cell.getStringCellValue();
                case STRING:
                    return cell.getStringCellValue();
                default:
                    return "";
            }
        } else {
            return "";
        }

    }


    /**
     * <p>将单元格内容转换为字符串</p>
     *
     * @param cell
     * @return
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC: //数字
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数，如取小数，值如0.0,取小数点后几位就写几个0
                DecimalFormat df = new DecimalFormat("0.00");
                returnValue = df.format(doubleValue);
                break;
            case STRING: //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN: //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK: // 空值
                break;
            case FORMULA: // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR: // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * <p>根据给定的实体类中赋值的注解值读取Excel</p>
     * <p>fileName:Excel文件路径</p>
     * <p>StatrRow：读取的开始行数（默认填0）</p>
     * <p>EndRow：读取的结束行数（填-1为全部）</p>
     * <p>sheetNums：导入第几个工作表（填-1为全部）</p>
     * <p>Class<T>：传过来的实体类类型</p>
     * <p>返回一个List<?>:?为实体类</p>
     *
     * @return
     */
    public static List<?> ReadExcelByAll(MultipartFile file, int StatrRow, int EndRow, int sheetNums, Class t) throws InvocationTargetException, IntrospectionException, InstantiationException, IllegalAccessException, NoSuchFieldException {
        //判断输入的开始值是否少于等于结束值
        if (StatrRow > EndRow && EndRow != -1) {
            /*logger.warn("输入的开始行值比结束行值大，请重新输入正确的行数");
            List<T> error = null;
            return error;*/
            throw new RuntimeException("输入的开始行值比结束行值大，请重新输入正确的行数");
        }
        if (sheetNums < -1) {
            List<T> error = null;
            return error;
        }
        //声明返回的结果集
        List<T> result = new ArrayList<>();
        //声明一个工作薄
        Workbook workbook = null;
        //声明一个文件输入流
        FileInputStream inputStream = null;
        try {
            // 获取Excel后缀名，判断文件类型
            String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
            // 获取Excel工作簿
            workbook = getWorkbook(file.getInputStream(), fileType);
            //处理Excel内容
            result = HandleData(workbook, StatrRow, EndRow, sheetNums, t);
            if (StatrRow > 0) {
                result.subList(0, StatrRow).clear();
            }else{
                result.subList(0, 1).clear();
            }
            System.out.println("----------------------");
        } catch (Exception e) {
            e.printStackTrace();
            //logger.warn("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            throw new RuntimeException("解析Excel失败，文件名：" + file.getOriginalFilename() + " 错误信息：" + e.getMessage());
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                /*//logger.warn("关闭数据流出错！错误信息：" + e.getMessage());
                return null;*/
                throw new RuntimeException("关闭数据流出错！错误信息：" + e.getMessage());
            }
        }
        return result;
    }

    /**
     * <p>处理Excel内容转为List<T>输出</p>
     * <p>workbook：已连接的工作薄</p>
     * <p>StatrRow：读取的开始行数（默认填0，0开始,传过来是EXcel的行数值默认从1开始，这里已处理减1）</p>
     * <p>EndRow：读取的结束行数（填-1为全部）</p>
     * <p>sheetNums：导入第几个工作表（填-1为全部）</p>
     * <p>Class<T>：所映射的实体类</p>
     */
    private static <T> List<T> HandleData(Workbook workbook, int StatrRow, int EndRow, int sheetNums, Class<?> t) throws IntrospectionException, NoSuchFieldException, IllegalAccessException, InstantiationException, InvocationTargetException, ClassNotFoundException {
        //声明返回的结果集
        List<T> result = new ArrayList<>();
        //判断导入的工作表
        int startSheet = 0;
        int totalSheet = workbook.getNumberOfSheets();
        if (sheetNums == -1) {
            startSheet = 0;
        } else if (sheetNums == 0) {
            return null;
        } else {
            startSheet = sheetNums - 1;
            totalSheet = sheetNums;
        }
        //解析sheet（sheet是Excel脚页）
        //循环工作表,特殊处理，不会影响速度
        for (int sheetNum = startSheet; sheetNum < totalSheet; sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            //获取头部数据
            //声明头部数据数列对象
            ArrayList<String> top = new ArrayList<>();
            //获取Excel第一行数据
            int firstRowNum = StatrRow;
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                //logger.warn("解析Excel失败，在第一行没有读取到任何数据！");
                continue;
            }
            for (int i = 0; i < firstRow.getLastCellNum(); i++) {
                top.add(convertCellValueToString(firstRow.getCell(i)));
            }
            //获取实体类的成原变量
            Map<String, Object> entityFields = getFieldAndValue(t);
            //判断所需要的数据列
            Map<String, Object> exceltoEntity = new HashMap<>();
            for (int i = 0; i < top.size(); i++) {
                if (entityFields.get(top.get(i)) != null && !"".equals(entityFields.get(top.get(i)))) {
                    exceltoEntity.put(String.valueOf(i), entityFields.get(top.get(i)));
                }
            }
            /*处理Excel数据内容*/
            int endRowNum;
            //获取结束行数
            if (EndRow == -1) {
                endRowNum = sheet.getPhysicalNumberOfRows();
            } else {
                endRowNum = EndRow <= sheet.getPhysicalNumberOfRows() ? EndRow : sheet.getPhysicalNumberOfRows();
            }
            List<Map<String, Object>> mapList = new ArrayList<>();
            //遍历行数
            for (int i = StatrRow - 1; i < endRowNum; i++) {
                Row row = sheet.getRow(i);
                if (null == row) {
                    continue;
                }
                //获取需要的列数据
                T texcel = (T) t.newInstance();
                for (Map.Entry<String, Object> map : exceltoEntity.entrySet()) {
                    //获取Exceld对应列的数据
                    String celldata = convertCellValueToString(row.getCell(Integer.parseInt(map.getKey())));
                    //使用发射
                    //获取实体类T中指定成员变量的对象
                    PropertyDescriptor pd = new PropertyDescriptor((String) map.getValue(), texcel.getClass());
                    //获取成员变量的set方法
                    Method method = pd.getWriteMethod();
                    //判断成员变量的类型
                    Field field = texcel.getClass().getDeclaredField((String) map.getValue());
                    String object = field.getGenericType().getTypeName();
                    if (object.endsWith("String")) {
                        //执行set方法
                        method.invoke(texcel, celldata);
                    }
                    if (object.endsWith("Double")) {
                        Double middata = Double.valueOf(celldata);
                        //执行set方法
                        method.invoke(texcel, middata);
                    }
                    if (object.endsWith("Float")) {
                        Float middata = Float.valueOf(celldata);
                        //执行set方法
                        method.invoke(texcel, middata);
                    }
                    if (object.endsWith("Integer")) {
                        Integer middata = Integer.parseInt(celldata);
                        //执行set方法
                        method.invoke(texcel, middata);
                    }
                }
                result.add(texcel);
            }
        }
        return result;
    }

    /**
     * 获取对应的实体类成员
     */
    private static Map<String, Object> getFieldAndValue(Class T) {
        //声明返回结果集
        Map<String, Object> result = new HashMap<>();
        Field[] fields = T.getDeclaredFields();//获取属性名
        if (fields != null) {
            for (Field field : fields) {
                ExcelRescoure Rescoure = field.getAnnotation(ExcelRescoure.class);
                if (Rescoure.name() != null && !"".equals(Rescoure.name())) {
                    result.put(Rescoure.name(), field.getName());
                }
            }
        } else {
            //logger.warn("实体类：" + T + "不存在成员变量");
            //return null;
            throw new RuntimeException("实体类：" + T + "不存在成员变量");
        }
        return result;
    }

    /**
     * @param list     导出的数据list
     * @param clazz    导出的对象class
     * @param fileName 导出的文件名称
     * @param response response
     * @param <T>
     */
    public static <T> void exportExcel(List<T> list, Class<T> clazz, String fileName, HttpServletResponse response) {
        SXSSFWorkbook workBook = getWorkBook(list, clazz);
        try {
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            OutputStream output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            bufferedOutPut.flush();
            workBook.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (Exception e) {
            //记录日志
            logger.info(e.getMessage());
            //throw new BadRequestException(e.getMessage());
        }
    }

    private static <T> SXSSFWorkbook getWorkBook(List<T> list, Class<T> clazz) {
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        // 单元格样式
        CellStyle titleStyle = titleStyle(wb);// 表头样式
        CellStyle orderStyle = orderStyle(wb);// 序号样式
        CellStyle contentStyle = contentStyle(wb);// 普通单元格样式

        // 遍历集合数据，产生数据行
        Iterator<?> it = list.iterator();
        int index = 0;//数据条数（行数）
        int rowIndex = 0;// 每新建一个sheet此数值归零
        Sheet sheet = null;
        while (it.hasNext()) {
            if (index == 0 || index % 5000 == 0) {//此处是限制单个sheet数据量，每5000条数据新建一个sheet
                rowIndex = 0;
                //创建新的sheet
                sheet = wb.createSheet();
                Row row = sheet.createRow(0);
                row.setHeightInPoints(23);
                Field[] declaredFields = getAllFields(clazz);
                //添加序号
                Cell cellOrder = row.createCell(0);
                cellOrder.setCellValue("序号");
                cellOrder.setCellStyle(titleStyle);
                sheet.setColumnWidth(0, 10 * 256);
                //设置表头
                for (Field field : declaredFields) {
                    ExcelRescoure annotation = field.getAnnotation(ExcelRescoure.class);
                    if (annotation != null) {
                        Cell cell = row.createCell(Integer.valueOf(annotation.sort()));
                        cell.setCellValue(annotation.name());
                        cell.setCellStyle(titleStyle);
                        //设置列的宽度
                        sheet.setColumnWidth(Integer.valueOf(annotation.sort()), (int) annotation.width() * 256);
                    }
                }
            }

            index++;
            rowIndex++;

            //设置序号值
            Row row = sheet.createRow(rowIndex);
            Cell cell = row.createCell(0);
            cell.setCellStyle(orderStyle);
            //cell.setCellValue(index);//新的sheet序号接着上个sheet的序号累加
            cell.setCellValue(rowIndex);//新的sheet序号从头开始累加
            row.setHeightInPoints(18);
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = getAllFields(t.getClass());
            for (Field field : fields) {
                ExcelRescoure annotation = field.getAnnotation(ExcelRescoure.class);
                if (annotation != null) {
                    cell = row.createCell(Integer.valueOf(annotation.sort()));
                    cell.setCellStyle(contentStyle);
                    String fieldName = field.getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    try {
                        Method getMethod = t.getClass().getMethod(getMethodName, new Class[]{});
                        Object value = getMethod.invoke(t, new Object[]{});
                        cell.setCellValue(null == value ? "" : String.valueOf(value));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return wb;
    }

    //设置标题样式
    private static CellStyle titleStyle(SXSSFWorkbook wb) {
        CellStyle cellStyle = contentStyle(wb);
        //设置居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置单元格背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置单元格填充样式(使用纯色背景颜色填充)
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置字体加粗
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        //设置字体
        font.setFontName("宋体");
        cellStyle.setFont(font);
        return cellStyle;
    }

    //设置单元格样式
    private static CellStyle contentStyle(SXSSFWorkbook wb) {
        //给单元格设置样式
        CellStyle cellStyle = wb.createCellStyle();
        Font font = wb.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 设置字体
        font.setFontName("宋体");
        // 给字体设置样式
        cellStyle.setFont(font);
        // 字体设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        // 设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    // 设置单元格样式
    private static CellStyle orderStyle(SXSSFWorkbook wb) {
        CellStyle cellStyle = contentStyle(wb);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        return cellStyle;
    }

    private static <T> Field[] getAllFields(Class<T> clazz) {
        Field[] declaredFields = clazz.getDeclaredFields();
        Class superClass = clazz.getSuperclass();
        if (!superClass.equals(Object.class)) {
            Field[] superFields = superClass.getDeclaredFields();
            int sonLength = declaredFields.length;
            int superLength = superFields.length;
            // 合并两个数组
            Field[] newFields = new Field[sonLength + superLength];
            System.arraycopy(declaredFields, 0, newFields, 0, sonLength);
            System.arraycopy(superFields, 0, newFields, sonLength, superLength);
            return newFields;
        }
        return declaredFields;
    }

    /**
     * 导入Excel
     */
    public static Workbook importExcel(MultipartFile file) throws IOException {
        //读取文件流
        InputStream is = file.getInputStream();
        //文件名
        String fileName = file.getOriginalFilename();
        //判断文件格式是否为excel
        boolean notNull = false;
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            //throw new BusinessException("上传文件格式不正确");
        }
        Workbook wb = null;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            //xlsx格式
            wb = new XSSFWorkbook(is);
        } else {
            //xls格式
            wb = new HSSFWorkbook(is);
        }
        return wb;
    }


}
